![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
System TuningHow well the system is tuned is also a major factor in the overall performance. For the most part, tuning the system involves resource allocation. By adding or removing a resource in the RDBMS or the OSor even the hardwareperformance can be drastically affected. The next few sections briefly introduce some of the tuning factors that can alter system performance. Chapters 9 through 15 detail these topics. Tuning RDBMS ResourcesThe RDBMS itself takes up many system resources; it uses these resources to serve the user requests. If Oracle does not have enough of a particular resource itself, you can see vast reductions in performance. Memory is of particular importance to Oracle. If Oracle does not have enough memory resources allocated for the block buffer cache or the data dictionary cache or even the log buffer, Oracle can be out of tune. If enough memory isnt allocated for the buffer cache or the data dictionary cache, you may see very low cache-hit rates. If enough memory isnt allocated for the log buffer, you may experience inefficient access to the redo log. Increasing the value of some of the Oracle resources such as the buffer cache, the shared pool, and the log buffer can improve cache-hit rates and thus reduce system I/O, resulting in improved performance. Careful analysis should be done to ensure that you are increasing the correct resource the right amount, as described in Chapter 9, Oracle Instance Tuning. You can use other Oracle tuning parameters to take advantage of special operating system features. For example, in the UNIX operating system, you have to tell Oracle that you have Asynchronous I/O (AIO) availableand you have to tell Oracle to use it. Chapter 12, Operating System-Specific Tuning, looks at some of these features. Tuning OS ResourcesThe operating system is typically tuned to allow Oracle to allocate needed resources. Many operating systems have limitations on how much memory or CPU time a single process or user can consume. Because Oracle is not a regular userit is a server processthese resources must be increased to allow Oracle to consume vast system resources. You may have to turn on other operating system features to allow Oracle to use them. Some features such as AIO (mentioned in the preceding section) are not turned on by default in some operating systems. Other new features are just becoming available. Many of these features are discussed in Chapter 12, Operating System-Specific Tuning. Sometimes, you must reduce operating system parameters to make room for Oracle to get resources. For example, you may have to reduce the OS file system buffers so that you can allocate more memory to Oracle (remember that Oracle bypasses the file system buffers). Tuning Hardware ResourcesHardware cannot be tuned in the same manner as the RDBMS and the OS. Although you can think of hardware tuning as more of a resource balancing act than a tuning exercise, the methodology is the same. After analyzing the system, you may discover that it is necessary to add hardware resources such as more disks, disk controllers, RAM, cache, CPUs, and so on. Adding hardware resources is all part of the tuning game and requires the same analysis and testing as RDBMS and OS tuning. Other Tuning FactorsThere may yet be other factors limiting the performance of your system, such as inefficiencies in the application code that cause unnecessary table scans or network limitations. Other processes running concurrently on the system may be stealing badly needed CPU cycles from Oracle. These processes may be using memory that could be allocated for Oracle; they could be causing contention on the same disk devices Oracle is using. When analyzing the performance of your system, try not to focus so much on the RDBMS that you miss something external to it. Many factors contribute to the databases performanceand these factors may not all be within the database itself. System LimitationsFinally, the performance of the system is limited by how fast the system can go. Even with the most optimally tuned system, you are limited by the speed of the systems CPUs. In this case, you have done everything possible to reduce bottlenecks; the system performance relies on the speed of the CPUs, as it should. When you are limited by how fast the computer runs, your job as a performance engineer is done. If you still need more performance, you need more CPUs or more systems (perhaps clusters). Many factors make it unwise to constantly run your system at its performance limits; it is better to leave a little capacity available for peak activity. SummaryThis chapter looked at what affects Oracle performance. Bottlenecks hurt performance in a big way. If these bottlenecks are not internal to the RDBMS, OS, or hardware, you can eliminate them and greatly improve performance. You can do much in the way of system tuning to help performance by allocating the proper resources to your RDBMS. In some cases, you may benefit from a larger data dictionary cache; in other cases, you may benefit from more block buffers. Chapter 9, Oracle Instance Tuning, looks at these things specifically. You should realize that there is only so much power in a system and that eventually you will reach that limitation. Once you exceed the capacity of the system and have done all possible optimization, it is time to move on and upgrade. The next few chapters look at these concepts in detail, starting with the Oracle instance, continuing with the operating system, and then dealing with the hardware.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |